﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml;
using System.Xml.Linq;
using System.Xml.Schema;
using System.Xml.XPath;

namespace SSIS2014To2012
{
    public class SSISConverter
    {
        public event EventHandler<ConversionEventArgs> StatusUpdated;

        private Dictionary<string, string> ElementTypeMap = new Dictionary<string, string>();
        private Dictionary<string, string> ComponentTypeMap = new Dictionary<string, string>();

        public SSISConverter()
        {

            LoadElementTypeMapping();
            LoadComponentTypeMapping();
        }

        public SSISConverter(string outputFolder)
            : this()
        {
            this.OutputFolder = outputFolder;
        }

        public string OutputFolder
        {
            get;
            set;
        }


        private void OnStatusUpdated(string statusMessage)
        {
            if (StatusUpdated != null)
                StatusUpdated(this, new ConversionEventArgs(statusMessage));
        }

        //Load XML mapping file for Element Types
        private void LoadElementTypeMapping()
        {
            XmlDocument maps = new XmlDocument();
            maps.Load("ElementTypeMaps.xml");

            var mapElements = maps.SelectNodes("/ElementTypeMap/ElementType");
            foreach (XmlNode x in mapElements)
            {
                //avoid any duplicates
                if(!ElementTypeMap.ContainsKey(x["NewType"].InnerText))
                    ElementTypeMap.Add(x["NewType"].InnerText, x["OldType"].InnerText);
            }
        }

        //Based on an element type, get the corresponding 2012 element type
        //returns a tuple which holds TRUE and the corresponding element type if found.
        //returns a tuple which holds FALSE and the original element type if not found.
        private Tuple<bool, string> GetOldElementType(string key)
        {
            if (ElementTypeMap.ContainsKey(key))
                return new Tuple<bool, string>(true, ElementTypeMap[key]);
            else
                return new Tuple<bool, string>(false, key);
        }

        //Load XML mapping file for Components
        private void LoadComponentTypeMapping()
        {
            XmlDocument maps = new XmlDocument();
            maps.Load("ComponentTypeMaps.xml");

            var mapElements = maps.SelectNodes("/ComponentTypeMap/ComponentType");
            foreach (XmlNode x in mapElements)
            {
                //avoid any duplicates
                if(!ComponentTypeMap.ContainsKey(x["ComponentName"].InnerText))
                    ComponentTypeMap.Add(x["ComponentName"].InnerText, x["ComponentGUID"].InnerText);
            }
        }

        //Based on the component type, get the corresponding 2012 Component GUID
        //returns a tuple which holds TRUE and the corresponding GUID if found
        //returns a tupke which holds FALSE and the original Component Name if not found
        private Tuple<bool, string> GetComponentGUID(string component)
        {
            if (ComponentTypeMap.ContainsKey(component))
                return new Tuple<bool, string>(true, ComponentTypeMap[component]);
            else
                return new Tuple<bool, string>(false, component);
        }

        //Perform steps to convert the package
        public void ConvertPackage(string sourcePackage)
        {
            XmlDocument doc = new XmlDocument();
            XmlNamespaceManager nsManager = new XmlNamespaceManager(doc.NameTable);
            
            nsManager.AddNamespace("DTS", "www.microsoft.com/SqlServer/Dts");
            try
            {
                doc.Load(sourcePackage);
                OnStatusUpdated(String.Format("Package File: {0} loaded successfully.", String.Format(@"..\{0}", Path.GetFileName(sourcePackage))));
            }
            catch(Exception e)
            {

                OnStatusUpdated(String.Format("Package File: {0} failed to load. (Exception is: {1})", sourcePackage, e.Message));
            }
            //change PackageFormatVersion from 8 to 6
            var packageFormatVersion = doc.SelectSingleNode("/DTS:Executable/DTS:Property[@DTS:Name='PackageFormatVersion']", nsManager);
            packageFormatVersion.InnerText = "6";
            OnStatusUpdated("PackageFormatVersion has been changed to 6.");

            //set all ExecutableTypes to be corresponding 2012 type
            var execs = doc.GetElementsByTagName("Executable", "www.microsoft.com/SqlServer/Dts");
            foreach (XmlNode n in execs)
            {
                string elementType = n.Attributes["DTS:ExecutableType"].InnerText;
                var t = GetOldElementType(elementType);
                n.Attributes["DTS:ExecutableType"].InnerText = t.Item2;
                n.Attributes["DTS:CreationName"].InnerText = t.Item2;

                if (!t.Item1)
                    OnStatusUpdated(String.Format("ElementType: '{0}' not be converted. No mapping type found.", elementType));
                else
                    OnStatusUpdated(String.Format("ElementType: '{0}' converted to ElementType: '{1}'", elementType, t.Item2));
            }
            
            //set all Component GUIDs
            var comps = doc.SelectNodes("DTS:Executable/DTS:Executables/DTS:Executable/DTS:ObjectData/pipeline/components/component", nsManager);
            foreach (XmlNode n in comps)
            {
                string componentType = n.Attributes["componentClassID"].InnerText;
                var t = GetComponentGUID(componentType);
                n.Attributes["componentClassID"].InnerText = t.Item2;

                if (!t.Item1)
                    OnStatusUpdated(String.Format("Component: '{0}' not be converted. No mapping type found.", componentType));
                else
                    OnStatusUpdated(String.Format("Component: '{0}' converted to the ComponentID: '{1}'", componentType, t.Item2));
                
            }

            //now save the converted package XML file
    
            string outputPathAndFile = Path.Combine(OutputFolder, Path.GetFileName(sourcePackage));
            try
            {
                doc.Save(outputPathAndFile);
                OnStatusUpdated(String.Format("Downgraded Package successfully saved to: {0}\n", outputPathAndFile));
            }
            catch (Exception e)
            {
                OnStatusUpdated(String.Format("Package File: {0} failed to save. (Exception is: {1})\n", Path.GetFileName(outputPathAndFile), e.Message));
            }
        }
    }
}
